<?php

class Grillas_adv_model2 extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    private function Strip($value) {
        if (get_magic_quotes_gpc() != 0) {
            if (is_array($value))
                if ($this->array_is_associative($value)) {
                    foreach ($value as $k => $v)
                        $tmp_val[$k] = stripslashes($v);
                    $value = $tmp_val;
                }
                else
                    for ($j = 0; $j < sizeof($value); $j++)
                        $value[$j] = stripslashes($value[$j]);
            else
                $value = stripslashes($value);
        }
        return $value;
    }

    private function getStringForGroup($group) {
        $i_ = '';
        $sopt = array('eq' => "=", 'ne' => "<>", 'lt' => "<", 'le' => "<=", 'gt' => ">", 'ge' => ">=", 'bw' => " {$i_}LIKE ", 'bn' => " NOT {$i_}LIKE ", 'in' => ' IN ', 'ni' => ' NOT IN', 'ew' => " {$i_}LIKE ", 'en' => " NOT {$i_}LIKE ", 'cn' => " {$i_}LIKE ", 'nc' => " NOT {$i_}LIKE ", 'nu' => 'IS NULL', 'nn' => 'IS NOT NULL');
        $s = "(";
        if (isset($group['groups']) && is_array($group['groups']) && count($group['groups']) > 0) {
            for ($j = 0; $j < count($group['groups']); $j++) {
                if (strlen($s) > 1) {
                    $s .= " " . $group['groupOp'] . " ";
                }
                try {
                    $dat = $this->getStringForGroup($group['groups'][$j]);
                    $s .= $dat;
                } catch (Exception $e) {
                    echo $e->getMessage();
                }
            }
        }
        if (isset($group['rules']) && count($group['rules']) > 0) {
            try {
                foreach ($group['rules'] as $key => $val) {
                    if (strlen($s) > 1) {
                        $s .= " " . $group['groupOp'] . " ";
                    }
                    $field = $val['field'];
                    $op = $val['op'];
                    $v = $val['data'];
                    if ($op) {
                        switch ($op) {
                            case 'bw':
                            case 'bn':
                                $s .= $field . ' ' . $sopt[$op] . "'$v%'";
                                break;
                            case 'ew':
                            case 'en':
                                $s .= $field . ' ' . $sopt[$op] . "'%$v'";
                                break;
                            case 'cn':
                            case 'nc':
                                $s .= $field . ' ' . $sopt[$op] . "'%$v%'";
                                break;
                            case 'in':
                            case 'ni':
                                $s .= $field . ' ' . $sopt[$op] . "( '$v' )";
                                break;
                            case 'nu':
                            case 'nn':
                                $s .= $field . ' ' . $sopt[$op] . " ";
                                break;
                            default :
                                $s .= $field . ' ' . $sopt[$op] . " '$v' ";
                                break;
                        }
                    }
                }
            } catch (Exception $e) {
                echo $e->getMessage();
            }
        }
        $s .= ")";
        if ($s == "()") {
            //return array("",$prm); // ignore groups that don't have rules
            return " 1=1 ";
        } else {
            return $s;
            ;
        }
    }

//fin private function getStringForGroup($group){

    private function array_is_associative($array) {
        if (is_array($array) && !empty($array)) {
            for ($iterator = count($array) - 1; $iterator; $iterator--) {
                if (!array_key_exists($iterator, $array)) {
                    return true;
                }
            }
            return!array_key_exists(0, $array);
        }
        return false;
    }
    public function sigueSolicitud($opc) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if($opc == "activos"){
            $filtro = " and contra.contratoEstado = 'A' ";
        }elseif($opc == "historico"){
            $fecIni = ($this->input->post("fecIni"))?$this->input->post("fecIni"):"1986-10-01";
            $fecFin = ($this->input->post("fecFin"))?$this->input->post("fecFin")." 23:59:59":"2986-10-01";
            $filtro = "and postu.postulacionFecha between '$fecIni' and '$fecFin' ";
        }

        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(contra.contratoId) as total from contratos contra
            inner join postulaciones postu on postu.postulacionesId = contra.postulacionesId
            inner join solservyase sol on sol.solServyAseId = postu.solServyAseId
            where postu.institucionId = '{$this->session->userdata("tablaUsuarioId")}' $filtro " . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select contra.contratoId,emps.empresaId,per.personasId,sol.solServyAseTipo,sol.solServyAseDescripcion,sol.solServyAseFechaCreo,sol.solServyAseNombre,sol.solServyAseSalBase,sol.solServyAseSalTope from contratos contra
            inner join postulaciones postu on postu.postulacionesId = contra.postulacionesId
            inner join solservyase sol on sol.solServyAseId = postu.solServyAseId
            left join empresas emps on emps.empresaId = sol.empresaId
            left join personas per on per.personasId = sol.personasId
            where postu.institucionId = '{$this->session->userdata("tablaUsuarioId")}' $filtro " . $wh." ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->contratoId;
            if(isset($value->empresaId)){
                $tipo = "Empresa";
                $valorID = $value->empresaId;
            }else{
                $tipo = "Persona";
                $valorID = $value->personasId;
            }
            $responce->rows[$i]['cell'] = array($tipo,$valorID, $value->solServyAseFechaCreo,$value->solServyAseNombre,$value->solServyAseDescripcion);
            $i++;
        }
        echo json_encode($responce);
   }

    public function reporteOfertaLab() {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        if($this->session->userdata("rolId") == "1")
            if($this->input->post("empresa") != "")
                $filtro = " ofer.empresaId = '{$this->input->post("empresa")}' ";
            else $filtro = " 1=1 ";
        else
            $filtro = " ofer.empresaId = '{$this->session->userdata("tablaUsuarioId")}' ";
        $estado = "";
        if($this->input->post('estado') == "A"){
            $estado = " and ofer.ofertaLaboralEstado = 'A'";
            $fecIni = ($this->input->post('fecIniCrea'))?$this->input->post('fecIniCrea'):"1986-01-01";
            $fecFin = ($this->input->post('fecFinCrea'))?$this->input->post('fecFinCrea'):"2086-10-01";
            $wh .= " and ofer.ofertaLaboralFechaCreo between '$fecIni' and '$fecFin'";
        }elseif($this->input->post('estado') == "C"){
            $estado = " and ofer.ofertaLaboralEstado = 'C'";
            $fecIni = ($this->input->post('fecIniCierre'))?$this->input->post('fecIniCierre'):"1986-01-01";
            $fecFin = ($this->input->post('fecFinCierre'))?$this->input->post('fecFinCierre'):"2086-10-01";
            $wh .= " and ofer.ofertaLaboralFechaCierre between '$fecIni' and '$fecFin'";
        }
        $wh .= ($this->input->post('cargo'))?" and ofer.ofertaLaboralCargo like '%{$this->input->post('cargo')}%'":"";
        $wh .= ($this->input->post('competencia'))?" and ofer.ofertaLaboralCompetencia like '%{$this->input->post('competencia')}%'":"";
        $wh .= ($this->input->post('perfil'))?" and ofer.ofertaLaboralPerfil like '%{$this->input->post('perfil')}%'":"";
        $wh .= ($this->input->post('descri'))?" and ofer.ofertaLaboralDescripcion like '%{$this->input->post('descri')}%'":"";
        $wh .= ($this->input->post('desde'))?" and ofer.ofertaLaboralRangoSalarioBase >= '{$this->input->post('desde')}'":"";
        $wh .= ($this->input->post('hasta'))?" and ofer.ofertaLaboralRangoSalarioTope <= '{$this->input->post('hasta')}'":"";
        $sql = "select count(ofer.ofertaLaboralId) as total from ofertaslaborales ofer where $filtro $estado" . $wh." group by ofer.ofertaLaboralEstado";
        $query = $this->db->query($sql);
        $count = $query->num_rows();
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select ofer.*,emp.`empresaRazon` from ofertaslaborales ofer 
                inner join empresas emp on emp.`empresaId` = ofer.`empresaId` where $filtro " . $wh;
        $this->session->set_userdata('consultaTemp',$sql);
        $sql = "select ofer.ofertaLaboralEstado,count(ofer.ofertaLaboralId) as total from ofertaslaborales ofer where $filtro $estado" . $wh." group by ofertaLaboralEstado ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        $sum = 0;
        foreach ($query->result() as $value) {
            $sum += $value->total;
            $responce->rows[$i]['id'] = $value->ofertaLaboralEstado;
            $tipo = "";
            if($value->ofertaLaboralEstado =="A")
                $tipo = "Total Abiertas";
            elseif($value->ofertaLaboralEstado =="C")
                $tipo = "Total Cerradas";
            $responce->rows[$i]['cell'] = array($tipo,$value->total);
            $i++;
        }
        $responce->rows[$i]['cell'] = array("TOTAL OFERTAS PUBLICADAS",$sum);
        echo json_encode($responce);
   }
   
    public function reportaPersonaContra($fecIni,$fecFin,$insti) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $estado = "";
        $fecIni = ($fecIni == "")?"1986-10-01":$fecIni;
        $fecFin = ($fecFin == "")?"2186-10-01":$fecFin;
        $wh .= ($insti != "")?" and postu.institucionId = '$insti'":"";
        $sql = "select count(contra.contratoId) as total from ofertaslaborales ofer
                inner join postulaciones postu on postu.ofertaLaboralId = ofer.ofertaLaboralId
                inner join contratos contra on contra.postulacionesId = postu.postulacionesId
                inner join personas per on per.personasId = postu.personasId
                where ofer.empresaId = '{$this->session->userdata("tablaUsuarioId")}' and contra.contratoFecha between '$fecIni' and '$fecFin' ".$wh." group by per.personasId";
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = (isset($obj[0]->total))?$obj[0]->total:0;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select per.personasId,per.personaTipoDocumento,per.personaDocumento,per.personaNombres,per.personaApellidos,per.personaGenero,per.personaEstrato,per.personaEstadoCivil,insti.institucionRazon from ofertaslaborales ofer
                inner join postulaciones postu on postu.ofertaLaboralId = ofer.ofertaLaboralId
                inner join contratos contra on contra.postulacionesId = postu.postulacionesId
                inner join personas per on per.personasId = postu.personasId
                left join instituciones insti on insti.institucionId = postu.institucionId
                where ofer.empresaId = '{$this->session->userdata("tablaUsuarioId")}' and contra.contratoFecha between '$fecIni' and '$fecFin' ".$wh." group by per.personasId ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->personasId;
            $tmp = (isset($value->institucionRazon))?$value->institucionRazon:"PERSONAL";
            $responce->rows[$i]['cell'] = array('','',$value->personaTipoDocumento,$value->personaDocumento,$value->personaNombres,$value->personaApellidos,$value->personaGenero,$value->personaEstrato,$value->personaEstadoCivil,$value->institucionRazon);
            $i++;
        }
        echo json_encode($responce);
   }
    public function reportaInstiContra($fecIni,$fecFin,$labor) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $inner = "";
        $fecIni = ($fecIni == "")?"1986-10-01":$fecIni;
        $fecFin = ($fecFin == "")?"2186-10-01":$fecFin;
        switch ($labor) {
            case 'O': 
                $inner = " inner join ofertaslaborales ofer on ofer.ofertaLaboralId = postu.ofertaLaboralId ";
                $wh .= " and ofer.empresaId = '{$this->session->userdata("tablaUsuarioId")}' and  postu.ofertaLaboralId is not null ";break;
            case 'S': 
                $inner = " inner join solservyase soli on soli.solServyAseId = postu.solServyAseId ";
                $wh .= " and soli.empresaId = '{$this->session->userdata("tablaUsuarioId")}' and  postu.solServyAseId is not null ";break;
            default:
                $inner = " left join solservyase soli on soli.solServyAseId = postu.solServyAseId and soli.empresaId = '{$this->session->userdata("tablaUsuarioId")}' 
                           left join ofertaslaborales ofer on ofer.ofertaLaboralId = postu.ofertaLaboralId and ofer.empresaId = '{$this->session->userdata("tablaUsuarioId")}' ";
                break;
        }
        $sql = "select count(contra.contratoId) as total from contratos contra
                inner join postulaciones postu on contra.postulacionesId = postu.postulacionesId
                inner join instituciones insti on insti.institucionId = postu.institucionId
                $inner
                where  contra.contratoFecha between '$fecIni' and '$fecFin' ".$wh." group by insti.institucionId";
//        exit($sql);
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = (isset($obj[0]->total))?$obj[0]->total:0;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select insti.institucionId,insti.institucionRazon,insti.institucionNit,contra.contratoFecha,postu.solServyAseId,postu.ofertaLaboralId from contratos contra
                inner join postulaciones postu on contra.postulacionesId = postu.postulacionesId
                inner join instituciones insti on insti.institucionId = postu.institucionId
                $inner
                where  contra.contratoFecha between '$fecIni' and '$fecFin' ".$wh." group by insti.institucionId ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->institucionId;
            $sql ="select postulacionesId from postulaciones postu
                    inner join solservyase soli on soli.solServyAseId = postu.solServyAseId where postu.institucionId = '{$value->institucionId}' and soli.empresaId = '{$this->session->userdata("tablaUsuarioId")}' and  postu.solServyAseId is not null  ";
            $tmpSol = ($this->db->query($sql)->num_rows() > 0)?"Si":"N/A";
            $sql ="select postulacionesId from postulaciones postu
                    inner join ofertaslaborales ofer on ofer.ofertaLaboralId = postu.ofertaLaboralId where postu.institucionId = '{$value->institucionId}' and ofer.empresaId = '{$this->session->userdata("tablaUsuarioId")}' and  postu.ofertaLaboralId is not null  ";
            $tmpOfer = ($this->db->query($sql)->num_rows() > 0)?"Si":"N/A";
            $responce->rows[$i]['cell'] = array('',$tmpSol,$tmpOfer,$value->contratoFecha,$value->institucionRazon,$value->institucionNit);
            $i++;
        }
        echo json_encode($responce);
   }
   public function reporteOfertaLabxInst() {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = $this->session->userdata("sqlOfer").$wh;
        $count = $this->db->query($sql)->num_rows();
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = $this->session->userdata("sqlOfer").$wh." ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->ofertaLaboralId;
            $responce->rows[$i]['cell'] = array($value->ofertaLaboralFechaCreo,$value->ofertaLaboralCargo,$value->ofertaLaboralPerfil,$value->postulacionFecha,$value->postulacionFechaPreseleccion,$value->contratoFecha,$value->contratoFechaCIerre,$value->contratoCausaCierre);
            $i++;
        }
        echo json_encode($responce);
   }
   public function reporteServAse($empresaId) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        if($empresaId == "") $filtro = " 1=1 ";
        else $filtro = " soli.empresaId = '$empresaId' ";
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        if($this->input->post("estado") == 'A'){
            $fecIni = ($this->input->post("fecIniCrea"))?$this->input->post("fecIniCrea"):"1986-10-01";
            $fecFin = ($this->input->post("fecFinCrea"))?$this->input->post("fecFinCrea"):"2986-10-01";
            $wh .= " and soli.solServyAseEstado = 'A' and soli.solServyAseFechaCreo between '$fecIni' and '$fecFin' ";
        }elseif($this->input->post("estado") == 'C'){
            $fecIni = ($this->input->post("fecIniCrea"))?$this->input->post("fecIniCierre"):"1986-10-01";
            $fecFin = ($this->input->post("fecFinCrea"))?$this->input->post("fecFinCierre"):"2986-10-01";
            $wh .= " and soli.solServyAseEstado = 'C' and soli.solServyAseFechaCierre between '$fecIni' and '$fecFin' ";
        }
        $wh .= ($this->input->post("tipo"))?" and soli.solServyAseTipo = '{$this->input->post("tipo")}'":"";
        $wh .= ($this->input->post("nombre"))?" and soli.solServyAseNombre like '%{$this->input->post("nombre")}%'":"";
        $wh .= ($this->input->post("descrip"))?" and soli.solServyAseDescripcion like '%{$this->input->post("descrip")}%'":"";
        $wh .= ($this->input->post("desde"))?" and soli.solServyAseSalBase >=  '{$this->input->post("desde")}'":"";
        $wh .= ($this->input->post("hasta"))?" and soli.solServyAseSalTope <=  '{$this->input->post("hasta")}'":"";
        $sql = "select soli.solServyAseId,soli.solServyAseTipo,soli.solServyAseDescripcion,soli.solServyAseNombre,soli.solServyAseFechaCreo,soli.solServyAseFechaCierre,soli.solServyAseCausaCierre,emp.`empresaRazon` from solservyase soli
                inner join empresas emp on emp.`empresaId` = soli.`empresaId`
                where $filtro ".$wh;
        $this->session->set_userdata('consultaTemp',$sql);
        $sql .= " group by soli.solServyAseEstado";
        $count = $this->db->query($sql)->num_rows();
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select soli.solServyAseEstado,count(soli.solServyAseId) as total from solservyase soli
                inner join empresas emp on emp.`empresaId` = soli.`empresaId`
                where $filtro ".$wh." group by soli.solServyAseEstado ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        $suma = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->solServyAseEstado;
            $suma += $value->total;
            $mensa = ($value->solServyAseEstado == "A")?"TOTAL ABIERTAS":"TOTAL CERRADAS";
            $responce->rows[$i]['cell'] = array($mensa,$value->total);
            $i++;
        }
        $responce->rows[$i]['cell'] = array("TOTAL SOLICITUDES",$suma);
        echo json_encode($responce);
   }
    public function indicaInstiContra($empresaId) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        if($empresaId == "") $filtro = " 1=1 ";
        else $filtro = " soli.empresaId = '$empresaId' ";
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        if($this->input->post("estado") == 'A'){
            $fecIni = ($this->input->post("fecIniCrea"))?$this->input->post("fecIniCrea"):"1986-10-01";
            $fecFin = ($this->input->post("fecFinCrea"))?$this->input->post("fecFinCrea"):"2986-10-01";
            $wh .= " and contra.`contratoEstado` = 'A' and contra.`contratoFecha` between '$fecIni' and '$fecFin' ";
        }elseif($this->input->post("estado") == 'C'){
            $fecIni = ($this->input->post("fecIniCrea"))?$this->input->post("fecIniCierre"):"1986-10-01";
            $fecFin = ($this->input->post("fecFinCrea"))?$this->input->post("fecFinCierre"):"2986-10-01";
            $wh .= " and contra.`contratoEstado` = 'I' and contra.`contratoFechaCIerre` between '$fecIni' and '$fecFin' ";
        }
        $wh .= ($this->input->post("tipo"))?" and soli.solServyAseTipo = '{$this->input->post("tipo")}'":"";
        $wh .= ($this->input->post("nombre"))?" and soli.solServyAseNombre like '%{$this->input->post("nombre")}%'":"";
        $wh .= ($this->input->post("descrip"))?" and soli.solServyAseDescripcion like '%{$this->input->post("descrip")}%'":"";
        $wh .= ($this->input->post("desde"))?" and soli.solServyAseSalBase >=  '{$this->input->post("desde")}'":"";
        $wh .= ($this->input->post("hasta"))?" and soli.solServyAseSalTope <=  '{$this->input->post("hasta")}'":"";
        $sql = "select soli.solServyAseId,soli.solServyAseTipo,soli.solServyAseDescripcion,soli.solServyAseNombre,soli.solServyAseFechaCreo,emp.`empresaRazon`,contra.`contratoFecha`,contra.`contratoCausaCierre`,contra.`contratoFechaCIerre`,insti.`institucionRazon`,empCal.`empCalServiCumplioExi`,empCal.`empCalServiCumplioHorario` from solservyase soli
                inner join postulaciones postu on postu.`solServyAseId` = soli.`solServyAseId`
                inner join instituciones insti on insti.`institucionId` = postu.`institucionId`
                inner join contratos contra on contra.`postulacionesId` = postu.`postulacionesId`
                left join empcalservi empCal on empCal.`solServyAseId` = soli.`solServyAseId`
                inner join empresas emp on emp.`empresaId` = soli.`empresaId`
                where $filtro ".$wh;
        $this->session->set_userdata('consultaTemp',$sql);
        $sql .= " group by contra.`contratoEstado`";
        $count = $this->db->query($sql)->num_rows();
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select contra.`contratoEstado`,count(soli.solServyAseId) as total from solservyase soli
                inner join postulaciones postu on postu.`solServyAseId` = soli.`solServyAseId`
                inner join contratos contra on contra.`postulacionesId` = postu.`postulacionesId`
                inner join empresas emp on emp.`empresaId` = soli.`empresaId`
                where $filtro ".$wh." group by contra.`contratoEstado` ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        $suma = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->contratoEstado;
            $suma += $value->total;
            $mensa = ($value->contratoEstado == "A")?"TOTAL CONTRATOS VIGENTES":"TOTAL CONTRATOS CERRADOS";
            $responce->rows[$i]['cell'] = array($mensa,$value->total);
            $i++;
        }
        $responce->rows[$i]['cell'] = array("TOTAL SOLICITUDES CONTRATADAS",$suma);
        echo json_encode($responce);
   }
    public function ofertasPreseleccion($institucionId) {
        /* Extra toda las ofertas laborales para las cueles fue postulada la institucion */
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx) $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(*) as total from preseleccioninstituciones pres 
        inner join ofertaslaborales ofer on ofer.`ofertaLaboralId` = pres.`ofertaLaboralId`
        inner join empresas emp on emp.`empresaId` = ofer.`empresaId`
        where pres.`institucionId` = '$institucionId' and pres.`preseInstEstado` = 'A'".$wh;
        $return = $this->db->query($sql)->result();
        $count = $return[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select pres.`preseInstiId`,emp.`empresaRazon`,ofer.`ofertaLaboralCargo`,ofer.`ofertaLaboralFechaCierre`  from preseleccioninstituciones pres 
        inner join ofertaslaborales ofer on ofer.`ofertaLaboralId` = pres.`ofertaLaboralId`
        inner join empresas emp on emp.`empresaId` = ofer.`empresaId`
        where pres.`institucionId` = '$institucionId' and pres.`preseInstEstado` = 'A' and ofer.`ofertaLaboralEstado` = 'A' ".$wh." ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        $suma = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->preseInstiId;
            $responce->rows[$i]['cell'] = array('',$value->empresaRazon,$value->ofertaLaboralCargo,$value->ofertaLaboralFechaCierre);
            $i++;
        }
        echo json_encode($responce);
   } //fin public function ofertasPreseleccion($institucionId) {
    public function enviaContrata($presInstId) {
        /* Extra todaa las personas que postulo para la oferta laboral de la empresa */
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx) $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(*) as total from preseleccioninstituciones pres 
        inner join postulaciones postu on postu.`ofertaLaboralId` = pres.`ofertaLaboralId` and postu.`institucionId` = pres.`institucionId`
        where pres.`preseInstiId` = '$presInstId' and pres.`preseInstEstado` = 'A' and postu.`postulacionEstado` = 'A' ".$wh;
        $return = $this->db->query($sql)->result();
        $count = $return[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select per.`personasId`,concat(per.`personaNombres`,' ',per.`personaApellidos`) as nombre,per.`personaGenero`,per.`personaTipoDocumento`,per.`personaDocumento`,postu.`postulacionPreseleccion`,postu.postulacionesId  from preseleccioninstituciones pres 
        inner join postulaciones postu on postu.`ofertaLaboralId` = pres.`ofertaLaboralId` and postu.`institucionId` = pres.`institucionId`
        inner join personas per on per.`personasId` = postu.`personasId`
        where pres.`preseInstiId` = '$presInstId' and pres.`preseInstEstado` = 'A' and postu.`postulacionEstado` = 'A' ".$wh." ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        $suma = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->postulacionesId;
            $responce->rows[$i]['cell'] = array('',$value->postulacionPreseleccion,$value->nombre,$value->personaTipoDocumento,$value->personaDocumento,$value->personaGenero);
            $i++;
        }
        echo json_encode($responce);
   } //fin public function ofertasPreseleccion($institucionId) {
    public function consultaInstiCiuServicios($servicios,$empresaId) {
        /* Extrae todas las instituciones que ofrecen los servicios que se consulta */
        $servicios = ($servicios == "")?"0":$servicios;
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx) $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select ciudadId from empresas where empresaId = '$empresaId'";
        $ciudadId = $this->db->query($sql)->result();
        $ciudadId = $ciudadId[0]->ciudadId;
        $sqlAuxSer = "";
        foreach($servicios as $serv){
            $sqlAuxSer .= " inner join instituciones_por_serviciosinclusion serInsti$serv on serInsti$serv.institucionId = insti.institucionId and serInsti$serv.servInclusionId = $serv ";
        }
        $sql = "select count(insti.institucionId) as total from instituciones insti
        $sqlAuxSer
        where insti.ciudadId = '$ciudadId' ".$wh;
        $return = $this->db->query($sql)->result();
        $count = $return[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select insti.institucionId,insti.institucionRazon from instituciones insti
        $sqlAuxSer
        where insti.ciudadId = '$ciudadId'".$wh." ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->institucionId;
            $responce->rows[$i]['cell'] = array($value->institucionId,$value->institucionRazon);
            $i++;
        }
        echo json_encode($responce);
   } //fin public function ofertasPreseleccion($institucionId) {
} //fin class
?>
